package com.example.myapplication;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * 书籍数据库管理类
 */
public class BookDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "books.db";
    private static final int DATABASE_VERSION = 1;
    
    private static final String TABLE_BOOKS = "books";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_BOOK_NUMBER = "book_number";
    private static final String COLUMN_BOOK_NAME = "book_name";
    private static final String COLUMN_USER_EMAIL = "user_email";

    public BookDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_BOOKS + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_BOOK_NUMBER + " TEXT NOT NULL, " +
                COLUMN_BOOK_NAME + " TEXT NOT NULL, " +
                COLUMN_USER_EMAIL + " TEXT NOT NULL)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKS);
        onCreate(db);
    }

    /**
     * 添加书籍
     */
    public long addBook(Book book) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_BOOK_NUMBER, book.getBookNumber());
        values.put(COLUMN_BOOK_NAME, book.getBookName());
        values.put(COLUMN_USER_EMAIL, book.getUserEmail());
        
        long id = db.insert(TABLE_BOOKS, null, values);
        db.close();
        return id;
    }

    /**
     * 删除书籍
     */
    public int deleteBook(int id, String userEmail) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_BOOKS, 
                COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?",
                new String[]{String.valueOf(id), userEmail});
        db.close();
        return result;
    }

    /**
     * 修改书籍
     */
    public int updateBook(Book book) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_BOOK_NUMBER, book.getBookNumber());
        values.put(COLUMN_BOOK_NAME, book.getBookName());
        
        int result = db.update(TABLE_BOOKS, values,
                COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?",
                new String[]{String.valueOf(book.getId()), book.getUserEmail()});
        db.close();
        return result;
    }

    /**
     * 查询书籍（支持空查询显示全部）
     */
    public List<Book> queryBooks(String bookNumber, String bookName, String userEmail) {
        List<Book> bookList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        StringBuilder selection = new StringBuilder(COLUMN_USER_EMAIL + " = ?");
        List<String> selectionArgs = new ArrayList<>();
        selectionArgs.add(userEmail);
        
        // 如果书号不为空，添加书号查询条件
        if (bookNumber != null && !bookNumber.trim().isEmpty()) {
            selection.append(" AND ").append(COLUMN_BOOK_NUMBER).append(" LIKE ?");
            selectionArgs.add("%" + bookNumber + "%");
        }
        
        // 如果书名不为空，添加书名查询条件
        if (bookName != null && !bookName.trim().isEmpty()) {
            selection.append(" AND ").append(COLUMN_BOOK_NAME).append(" LIKE ?");
            selectionArgs.add("%" + bookName + "%");
        }
        
        Cursor cursor = db.query(TABLE_BOOKS, null, selection.toString(),
                selectionArgs.toArray(new String[0]), null, null, null);
        
        if (cursor.moveToFirst()) {
            do {
                Book book = new Book();
                book.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)));
                book.setBookNumber(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NUMBER)));
                book.setBookName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BOOK_NAME)));
                book.setUserEmail(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USER_EMAIL)));
                bookList.add(book);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return bookList;
    }
}
